﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Globalization;
using System.IO;
using AsLib;
using AsPlugInManager;

namespace NNLInterface
{
    public partial class frmForecastImport : NNLInterface.frmBaseBasic
    {
        #region 初始化

        private string strDepartNo;
        string strSQLHead = "select * from vie_ForeCastData ";
        string strTranNo = "";
        DataTable dtExcel;

        public frmForecastImport()
        {
            InitializeComponent();
            this.Load += new EventHandler(frmOrderImport_Load);

        }

        public frmForecastImport(IASPArgList argList)
        {
            InitializeComponent();
            this.grpHead.Visible = false;
            this.SetToolVisible(common.ToolVisible.ImportData);
            //this.CmdSearch.Caption = "读取Excel";
            this.CmdSearch.Caption = "读取";
            this.SearchButtonVisibility = true;
            this.ExcelButtonVisibility=true;
            common.agrs = (ASPArgList)argList;
            this.Load += new EventHandler(frmOrderImport_Load);
        }

        void frmOrderImport_Load(object sender, EventArgs e)
        {

            IniDataSource();

        }

        private void frmOrderImport_FormClosing(object sender, FormClosingEventArgs e)
        {
            serial.SerializeBinary(common.sysParameter, Application.StartupPath + @"\NNLInterface.dat");
            common._its_accessdb = null;
            common.pro = null;
            GC.Collect();
        }


        private void IniDataSource()
        {
            string strSQL = strSQLHead + " where 1=2";
            common.SubShowList(accessdb.GetRecord(strSQL).ADODataRst, this.ListGrid, this.ListView, false);
            common.subIniGrid(this.ListView, common.enumGridStyle.ViewStyle);

            common.GetResourceString(this, this.Name);
            common.RestoreFormLayOut(this, this.layOut, this.grpHead);
            common.ReStoreLayOut(this.ListView);


        }
        #endregion



        #region 工具栏按钮事件

        private void frmOrderImport_ToolButtonClick(object sender, string e)
        {
            try
            {
                switch (e)
                {
                    case "search":

                        //if (this.btnExcel.Text == "")
                        //{
                        //    DevExpress.XtraEditors.XtraMessageBox.Show("请选择一个正确的Excel文件!", common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                        //    this.btnExcel.Focus();
                        //    return;
                        //}

                        Cursor currentCursor = Cursor.Current;
                        Cursor.Current = Cursors.WaitCursor;

                        //读取Excel文件
                        //ReadExcel(btnExcel.Text);

                        //2012-07-30直接从MC端的forecast导入的数据中取得
                        if (!showForeCastData()) return;

                        Cursor.Current = currentCursor;
                        break;
                    case "import":
                        subExportData();
                        break;
                    case "excel":
                        common.subExportXLS(this.ListView);
                        break;
                    case "print":
                        common.PrintView(this.ListGrid, "工单数据");
                        break;
                    case "exit":
                        common.SaveFormLayOut(this, this.layOut, this.grpHead);
                        common.SaveLayout(this.ListView);
                        this.Dispose();
                        break;

                    default:
                        {
                            break;
                        }
                }


            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message, common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

        }

        private bool showForeCastData()
        {
            try
            {
                string strSQL = "select * from vie_ForeCastData";
                DataTable dtData = accessdb.GetRecord(strSQL).ADODataRst;
                common.SubShowList(dtData, this.ListGrid, this.ListView, false);
                return true;
            }
            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("读取数据错误!" + "\n" + ex.Message, common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
        }


        private void ReadExcel(string fileName)
        {

            Excel.Application xApp = new Excel.ApplicationClass(); ;
            Excel.Workbook xBook;

            try
            {
                //得到WorkBook对象, 可以用两种方式之一: 下面的是打开已有的文件 
                xApp.Workbooks._Open(fileName,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value
                , Missing.Value, Missing.Value, Missing.Value, Missing.Value);
                //ListGrid.DataSource = ReadOrderData(xApp, xBook).DefaultView;
                xApp.Visible = false;


                xBook = xApp.Workbooks[1];
                Excel.Worksheet sheet = (Excel.Worksheet)xApp.ActiveSheet;// 获取当前工作表
                int colStart = 5;
                //取得日期序列
                List<string> list = new List<string>();
                while (common.GetCellData(xApp, sheet, 1, colStart, true).Trim() != "")
                {
                    list.Add(common.GetCellData(xApp, sheet, 1, colStart, true));
                    colStart++;
                }
                //sheet = null;
                //xBook = null;
                xApp.Quit();
                KillExcelProcess.Kill(xApp);//调用kill当前excel进程 
                dtExcel = ExcelImport.ExcelToDS(fileName);
                if (!CheckData()) return;
                ReadOrderData(list);
                DevExpress.XtraEditors.XtraMessageBox.Show("读取Excel文件成功!\r\n" + "共读取" + ListView.RowCount.ToString() + "笔有效数据", common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);

            }

            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("读取文件错误!" + "\n" + ex.Message, common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
            }

            finally
            {


            }


        }


        private bool CheckData()
        {

            if (dtExcel.Select("isnull(母件代号,'')=''").Length >= 1)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("Excel文件中存在母件代号为空的的数据,请检查!", common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                return false;
            }
            return true;
        }



        //注意：用此方法写的数据速度很快，但是要将Excel的格式进行设定才可以
        private void ReadOrderData(List<string> list)
        {
            DataTable dtData = accessdb.GetRecord("SELECT oie.CustNo, oie.ProductNo, oie.CustStock, oie.PlanDate, oie.Qty,[size] "
                                                  + " FROM OrderInExlTmp oie where 1=2").ADODataRst;
            int colStart = 4;
            foreach (string item in list)
            {
                string dtePlan = item;
                for (int j = 0; j < dtExcel.Rows.Count; j++)
                {
                    string custNo = dtExcel.Rows[j][0].ToString();
                    string ItemNo = dtExcel.Rows[j][2].ToString();
                    string strSize= dtExcel.Rows[j][1].ToString();
                    string custStock = dtExcel.Rows[j][3].ToString() == "" ? "0" : dtExcel.Rows[j][3].ToString();
                    string planQty = dtExcel.Rows[j][colStart].ToString() == "" ? "0" : dtExcel.Rows[j][colStart].ToString();
                    if (common.IsDateTime(dtePlan))
                    {

                        dtData.Rows.Add(new object[] {custNo,ItemNo, custStock,         //品目
                                            System.Convert.ToDateTime( dtePlan),           //EST
                                            Convert.ToDouble(planQty),                    //数量
                                            strSize });                                   //规格        

                    }

                }
                colStart++;
            }


            common.SubShowList(dtData, this.ListGrid, this.ListView, false);
        }




        private void subPrint()
        {
            if (!common.CheckExistsData(this.ListView)) return;
            common.PrintView(this.ListGrid, "工单数据");
        }



        private DataTable SearchData()
        {
            DataTable dt = new DataTable();
            string strSQL = strSQLHead + "   where TranNo='" + strTranNo + "'";

            dt = accessdb.GetRecord(strSQL).ADODataRst;
            return dt;
        }

        private void subExportData()
        {
            Cursor currentCursor = Cursor.Current;

            try
            {
                if (!common.CheckExistsData(this.ListView)) return;
                DataTable viewt = (DataTable)this.ListGrid.DataSource;


                if (DevExpress.XtraEditors.XtraMessageBox.Show("确定要将所选择数据导入Asprova中吗(Y/N)?\n"
                                                                , common.sysParameter.MessageInf, MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.No) return;

                //判断如果有其他的的用户导入,如果SysProSwitch中的开关为真，则不能时行导入
                string strCheck = "SELECT * FROM SysProSwitch sps WHERE sps.ProcessType='OrderImport' AND sps.OperationProcess=1";
                if (accessdb.GetRecord(strCheck).RecordNumber >= 1)
                {
                    DevExpress.XtraEditors.XtraMessageBox.Show("其他用户正在使用导入功能，请稍后再试!", common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;

                }

                Cursor.Current = Cursors.WaitCursor;
                //锁定当前用户
                //将正在导入的状态置为1
                string strupdateSwitch = "UPDATE sps SET sps.OperationProcess = 1 FROM SysProSwitch sps "
                                         + " WHERE sps.ProcessType='OrderImport'  ";
                if (!accessdb.RsChange(strupdateSwitch)) return;


                //生成写入数据

                //生成当前的最大的版本号
                //string strOrderType = "FC" + strDepartNo;
                //strTranNo = Convert.ToString(accessdb.GetField("SELECT   '" + strOrderType + "' + replace(dbo.GetShortDate(GETDATE()),'-','')"));
                //strTranNo = Convert.ToString(accessdb.ExecuteSP("pro_GetMaxNoteNo", common.Choose.HasAOutput, new object[] { strTranNo, 3, "ORDERINF", strTranNo }));


                //string strSqlInsert = " set xact_abort on begin tran \r\n "
                //                    + "  delete from OrderInExlTmp " + "\r\n";

                ////写入当前的EXCEL数据
                //for (int i = 0; i < viewt.Rows.Count; i++)
                //{
                //    strSqlInsert += " INSERT INTO OrderInExlTmp \n"
                //                   + "( \n"
                //                   + "	CustNo, \n"
                //                   + "	ProductNo, \n"
                //                   + "	CustStock, \n"
                //                   + "	PlanDate, \n"
                //                   + "	Qty, \n"
                //                   + "	TranNo, \n"
                //                   + "	size, \n"
                //                   + "	EntDate \n"
                //                   + ") \n"
                //                   + "VALUES \n"
                //                   + "('"
                //                   + viewt.Rows[i]["CustNo"] + "','"
                //                   + viewt.Rows[i]["ProductNo"] + "',"
                //                   + viewt.Rows[i]["CustStock"] + ",'"
                //                   + viewt.Rows[i]["PlanDate"] + "',"
                //                   + viewt.Rows[i]["Qty"] + ",'"
                //                   + strTranNo
                //                   + "','" + viewt.Rows[i]["Size"] 
                //                   + "',getdate())\r\n ";


                //}



                //写入数据
                string strSqlInsert = string.Format(" EXEC pro_CreateOrderData ''");

                //strSqlInsert += "  commit tran";

                if (!accessdb.RsChange(strSqlInsert))
                {
                    //如果BOM数据不存在，则需要显示出来
                    //DataTable dtItemCode = accessdb.GetRecord("select * from ItemBomCheck ").ADODataRst;
                    //if (dtItemCode.Rows.Count >= 1)
                    //{
                    //    frmInformation frminformation = new frmInformation(dtItemCode, "产品料号在ERP中不存在列表", "orderImport");
                    //    frminformation.ShowDialog();
                    //}
                    DevExpress.XtraEditors.XtraMessageBox.Show("处理订单数据出错!", common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);

                    return;
                }

                //导入订单
                common.subImportToAsprova("Orders", "OrdersIn", "");

                //解锁用户
                UnlockUser();

                //如果成功，则提示
                DevExpress.XtraEditors.XtraMessageBox.Show("ForeCast订单数据已成功导入Asprova中!\n" , common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);
                this.btnExcel.Text = "";
                this.ListGrid.DataSource = null;
            }

            catch (Exception ex)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(ex.Message, common.sysParameter.MessageInf, MessageBoxButtons.OK, MessageBoxIcon.Information);

                UnlockUser();


            }
            finally
            {
                //解锁用户
                UnlockUser();
                Cursor.Current = currentCursor;
            }

        }

        private void UnlockUser()
        {
            //解锁用户
            string strSQL = " UPDATE sps SET sps.OperationProcess = 0 FROM SysProSwitch sps "
                         + " WHERE sps.ProcessType='OrderImport'  ";
            accessdb.RsChange(strSQL);
        }

        private bool CheckItemData(DataTable viewt)
        {
            ASBItem itemCode;
            DataTable dtItemCode = new DataTable();
            dtItemCode.Columns.Add("ItemCode", typeof(string));
            bool flag;
            for (int i = 0; i < viewt.Rows.Count; i++)
            {
                flag = false;
                string itemCodeExcel = viewt.Rows[i]["ItemCode"].ToString().Replace("\r", "").Replace("\n", "").Replace("_", "");
                for (int ii = 1; ii <= common.pro.RootItem.ChildCount; ii++)
                {
                    itemCode = common.pro.RootItem.get_ChildAsItem(ii);
                    string itemCodeAPS = itemCode.Code;
                    if (itemCodeExcel == itemCodeAPS)
                    {
                        flag = true;
                        break;

                    }
                }
                if (!flag)
                {
                    dtItemCode.Rows.Add(new object[] { itemCodeExcel });
                }
            }
            if (dtItemCode.Rows.Count >= 1) //如果品目在APS中不存在，则需要显示提示信息
            {

                return false;
            }
            return true;
        }



        #endregion

        private void btnExcel_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {
            this.btnExcel.Text = common.ShowOpenFileDialog("打开Excel文档", "Microsoft Excel(*.xls)|*.xls");

        }
    }
}